/*********************************************************************************
 *      Copyright:  (C) 2022 zengwen<1825984037@qq.com>
 *                  All rights reserved.
 *
 *       Filename:  data_sqlite3.c
 *    Description:  This file 
 *                 
 *        Version:  1.0.0(19/07/22)
 *         Author:  zengwen <1825984037@qq.com>
 *      ChangeLog:  1, Release initial version on "19/07/22 20:51:58"
 *                 
 ********************************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <errno.h>

#include "sqlite3.h"
#include "data_sqlite3.h"

/* 打开数据库 */
int open_data_sqlite (sqlite3 **db,const char *db_file)
{
	int            ret;
	char          *errmsg = 0;

	ret=sqlite3_open(db_file,db);//打开数据库标识

	if(ret!=SQLITE_OK)
	{
		printf("Failed to open database!:%s\n",sqlite3_errmsg(*db));
		return -1;
	}
	printf("Open database sucessfuly!\n");

	char  *sql="CREATE TABLE IF NOT EXISTS temperature_mqtt(\
				ID INTEGER PRIMARY KEY AUTOINCREMENT,\
				cur_time     TEXT,\
				temper       REAL,\
				humidity     REAL\
				 );";

	ret=sqlite3_exec(*db,sql,NULL,NULL,&errmsg);

	if(ret != SQLITE_OK)
	{
		printf("Create table error:%s\n",errmsg);
		sqlite3_free(errmsg);
		return -2;
	}
	printf("Table create successfully\n");

	return 0;
}

/* 保存数据到数据库 */
int save_data_sqlite(sqlite3 *db,char Data[])
{
	char        *cur_time = NULL;
	char        *temp = NULL;
	char        *humi = NULL;
	float        temper;
 	float        humidity;
	char         sql_in[256];
	int          rv=0;
	char        *errmsg=0;
	const char   s[2] = "/";

	cur_time = strtok(Data,s );
	temp = strtok(NULL,s );
	humi = strtok(NULL,s);

	if(cur_time!=NULL && temp!=NULL &&humi!=NULL )
	{
		temper = atof(temp);
		humidity = atof(humi);

		snprintf(sql_in,sizeof(sql_in), "insert into \"temperature_mqtt\" values(NULL,'%s','%f','%f');", cur_time, temper,humidity);
		rv=sqlite3_exec(db,sql_in,NULL,NULL,&errmsg);

		if (rv == SQLITE_OK)
		{
			printf("save data successfully!\n");
		}
		else
		{
			printf("Failed to save  data:%s\n",strerror(errno));
			return -1;
		}
	}

	sqlite3_free(errmsg);

	return 0;
}

/* 取数据库保存的数据 */
int get_data_sqlite(sqlite3 *db,char Buf[])
{
	int           rv=-1;
	char         *sql="SELECT * FROM temperature_mqtt LIMIT 1;";
	const char   *error=NULL;
	sqlite3_stmt *pstmt;
	int           nCol = 1;
    char         *cur_time = NULL;
	char         *temp = NULL;
	char         *humi = NULL;
	float         temper;
	float         humidity;

	rv = sqlite3_prepare_v2(db, sql, strlen(sql), &pstmt, &error);

	if( rv== SQLITE_OK )
	{
		if(sqlite3_step( pstmt ) == SQLITE_ROW)
		{
			cur_time = (char *)sqlite3_column_text(pstmt,1);
			temp = (char *)sqlite3_column_text(pstmt,2);
			humi = (char *)sqlite3_column_text(pstmt,3);

			temper = atof(temp);
			humidity = atof(humi);

			if(cur_time != NULL)
			{
				snprintf(Buf,32,"%s/%f/%f",cur_time,temper,humidity);
				return 1;
			}
			else
			{
				printf ("select from table failure or table is null.\n");
				return -1;
			}

		}
	}

	return 0;
}

/* 删除数据库已发送的数据 */
int delete_data_sqlite(sqlite3 *db)
{
	int    rv = -1;
	char  *error = NULL;
	char  *sql = "delete from  temperature_mqtt where rowid in (select rowid from temperature_mqtt LIMIT 1);";

	rv = sqlite3_exec(db, sql, NULL, NULL, &error);

	if( rv < 0)
	{
		printf("delete data form table failure:%s\n",strerror(errno));
		sqlite3_free(error);
		return -1;
	}
	printf("delece a record.\n");
	return 0;
}

